﻿using System;
using System.Data;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Linq.Dynamic;
using System.Data.Linq.Mapping;
using System.Collections.ObjectModel;
using System.ComponentModel;

using Infrastructure.Diagnostics;
using TempEmployees.DAL;

namespace TempEmployees.BLL.Database
{
    public class DbQuery
    {
        public static TempEmpDatabaseDataContext Database { get { return new TempEmpDatabaseDataContext(); } }

        private static readonly string QUERY_AND = " AND ";
        private static readonly string QUERY_OR = " OR ";
        private static readonly string QUERY_FILTER = "{0} == @{1}";

        public static List<T> GetTableRows<T>(Filters filters) where T : class
        {
            IQueryable dbTable = Database.GetTable<T>();
            List<T> rows = new List<T>();
            if (filters == null || filters.Count == 0)
            {
                var query = dbTable;
                foreach (var row in query)
                {
                    rows.Add((T)row);
                }
            }
            else
            {
                List<object> values = new List<object>();
                string queryStr = GetQueryString(filters, values);
                var query = dbTable.Where(queryStr, values.ToArray());
                foreach (var row in query)
                {
                    rows.Add((T)row);
                }
            }
            return rows;
        }

        public static List<T> GetTableRows<T>(string key, object value) where T : class
        {
            Filters filters = new Filters();
            filters.Add(key, value);
            return GetTableRows<T>(filters);
        }

        public static List<T> GetTableRows<T>(Filters filters, string orderByField) where T : class
        {
            var dbTable = Database.GetTable<T>();
            if (orderByField.Length == 0) return null;
            List<T> rows = new List<T>();
            if (filters == null || filters.Count == 0)
            {
                var query = dbTable.OrderBy(orderByField);
                foreach (var row in query)
                {
                    rows.Add((T)row);
                }
            }
            else
            {
                List<object> values = new List<object>();
                string queryStr = GetQueryString(filters, values);
                var query = dbTable.Where(queryStr, values.ToArray()).OrderBy(orderByField);
                foreach (var row in query)
                {
                    rows.Add((T)row);
                }
            }
            return rows;
        }

        public static T GetSingleRow<T>(string key, object value) where T : class
        {
            Filters filters = new Filters();
            filters.Add(key, value);
            return GetSingleRow<T>(filters);
        }

        public static T GetSingleRow<T>(Filters filters) where T : class
        {
            List<T> list = GetTableRows<T>(filters);
            return (list.Count > 0) ? list[0] : null;
        }

        private static string GetQueryString(Filters filters, List<object> values)
        {
            StringBuilder queryStr = new StringBuilder();
            int count = 0;
            foreach (string key in filters.Keys)
            {
                if (filters[key] is List<object>)
                {
                    if (queryStr.Length > 0) queryStr.Append(QUERY_AND);
                    queryStr.Append(" ( ");
                    bool first = true;
                    List<object> valueList = (List<object>)filters[key];
                    foreach (object value in valueList)
                    {
                        if (!first) queryStr.Append(QUERY_OR);
                        queryStr.Append(string.Format(QUERY_FILTER, key, count++));
                        values.Add(value);
                        if (first) first = false;
                    }
                    queryStr.Append(" ) ");
                }
                else
                {
                    if (queryStr.Length > 0) queryStr.Append(QUERY_AND);
                    queryStr.Append(string.Format(QUERY_FILTER, key, count++));
                    values.Add(filters[key]);
                }
            }
            return queryStr.ToString();
        }

        #region Generic repository
        // http://www.c-sharpcorner.com/uploadfile/scottlysle/genericl2sincs07212008041344am/genericl2sincs.aspx

        /// <summary>
        /// Select by Primary Key Value
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id">The PK value to search for</param>
        /// <returns>Single matching PK to id</returns>
        public static T SelectByPK<T>(string id) where T : class
        {
            try
            {
                /*using (*/TempEmpDatabaseDataContext context = Database;//)
                {
                    // get the table by the type passed in
                    var table = context.GetTable<T>();

                    // get the metamodel mappings (database to domain objects)
                    MetaModel modelMap = table.Context.Mapping;

                    // get the data members for this type
                    ReadOnlyCollection<MetaDataMember> dataMembers = modelMap.GetMetaType(typeof(T)).DataMembers;

                    // find the primary key field name by checking for IsPrimaryKey
                    string pk = (dataMembers.Single<MetaDataMember>(m => m.IsPrimaryKey)).Name;

                    // return a single object where the id argument matches the primary key field value
                    return table.SingleOrDefault<T>(delegate(T t)
                    {
                        string memberId = t.GetType().GetProperty(pk).GetValue(t, null).ToString();
                        return memberId.ToString() == id.ToString();
                    });
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        /// <summary>
        /// Select All by Type
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns>Matching table as typed list</returns>
        public static IList<T> SelectAll<T>() where T : class
        {
            try
            {
                /*using (*/TempEmpDatabaseDataContext context = Database;//)
                {
                    // get the table matching the type and return all of it as typed list
                    var table = context.GetTable<T>().ToList<T>();
                    return table;
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        /// <summary>
        /// Insert an Object into a Table
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="item"></param>
        public static bool Insert<T>(T item) where T : class
        {
            try
            {
                // get the approapriate table by type and then insert the passed in object and submit
                // that change to the data context
                /*using (*/TempEmpDatabaseDataContext context = Database;//)
                {
                    // get the table by the type passed in
                    var table = context.GetTable<T>();

                    // pass in the object with insert on submit and then submit changes
                    table.InsertOnSubmit(item);
                    context.SubmitChanges();
                    return true;
                }
            }
            catch (Exception e)
            {
                Logger.Error(Logger.ExceptionMessage(e));
                return false;
            }
        }

        public static bool InsertAll<T>(List<T> items) where T : class
        {
            try
            {
                // get the approapriate table by type and then insert the passed in object and submit
                // that change to the data context
                /*using (*/TempEmpDatabaseDataContext context = Database;//)
                {
                    // get the table by the type passed in
                    var table = context.GetTable<T>();

                    // pass in the object with insert on submit and then submit changes
                    table.InsertAllOnSubmit<T>(items);
                    context.SubmitChanges();
                    return true;
                }
            }
            catch (Exception e)
            {
                Logger.Error(Logger.ExceptionMessage(e));
                return false;
            }
        }

        /// <summary>
        /// Update an Existing Object
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="item">The object to update</param>
        public static bool Update<T>(T item, T original) where T : class
        {
            try
            {
                //// create a new instance of the object
                Object newObj = Activator.CreateInstance(typeof(T), new object[0]);

                PropertyDescriptorCollection originalProps = TypeDescriptor.GetProperties(item);

                // set the new object to match the passed in object
                foreach (PropertyDescriptor currentProp in originalProps)
                {
                    if (currentProp.Attributes[typeof(System.Data.Linq.Mapping.ColumnAttribute)] != null)
                    {
                        object val = currentProp.GetValue(item);
                        currentProp.SetValue(newObj, val);
                    }
                }

                // attach the new object to a new data context and call submit changes on the context
                /*using (*/TempEmpDatabaseDataContext context = Database;//)
                {
                    var table = context.GetTable<T>();
                    table.Attach((T)newObj, original);
                    context.SubmitChanges();
                }
                return true;
            }
            catch (Exception e)
            {
                Logger.Error(Logger.ExceptionMessage(e));
                return false;
            }
        }

        #endregion
    }
}
